package service

import (
	"fmt"
	"github.com/gofiber/fiber/v2"
	"guns-excel/internal/pkg/database"
	"guns-excel/internal/pkg/util"
	excel_util "guns-excel/internal/pkg/util/excel-util"
	"strings"
	"time"
)

type ModelE struct {
	ModelField string `json:"modelField"` // 对应字段名
	Value      string `json:"value"`      // 对应值
	CellName   string `json:"CellName"`   // 对应查询的cell名称
}

// excel 返回的数据源格式
type ModelMeta struct {
	TableSchema string   `json:"tableSchema"` // 库名
	TableName   string   `json:"tableName"`   // 表名
	ModelEList  []ModelE `json:"modelEList"`  // excel模型返回的数据
}

func Test1(ctx *fiber.Ctx) *ModelMeta {
	// 模拟文件路径,sheet， 对应的调度模型
	//excel_util.ReadExcel("file/fadianliang.xlsx", "年度", "D8", "C8")
	// 获取对应的模型
	modelExcels, err := GetModelExcelByModelId(ctx, "250058572359012352")
	if err != nil {
		return nil
	}
	cells := make([]string, 0)
	modelMeta := new(ModelMeta)
	for _, excel := range modelExcels {
		// 根据id获取对应字段的规则
		rule, err := GetRuleByModelExcelId(ctx, excel.Id)
		if err != nil {
			continue
		}
		model, err := GetModelByModelId(ctx, excel.ModelId)
		if err != nil {
			continue
		}
		m := new(ModelE)
		m.ModelField = excel.ModelField
		m.CellName = rule.StartCell // 后续这里是实际的cell单元格位置
		cells = append(cells, rule.StartCell)
		// 记录库名和表名
		if modelMeta.TableSchema == "" {
			modelMeta.TableSchema = model.TableSchema
		}
		if modelMeta.TableName == "" {
			modelMeta.TableName = model.TableName
		}
		modelMeta.ModelEList = append(modelMeta.ModelEList, *m)
	}
	excel, err := excel_util.ReadExcel("file/fadianliang.xlsx", "年度", cells...)
	if err != nil {
		return nil
	}
	// 为返回的数据源格式赋值
	for k, v := range excel {
		for i, cell := range modelMeta.ModelEList {
			if cell.CellName == k {
				modelMeta.ModelEList[i].Value = v
			}
		}
	}
	sql := SplicingSql(modelMeta)
	if err = database.MysqlDB.Exec(sql).Error; err != nil {
		return nil
	}
	return modelMeta
}

func SplicingSql(meta *ModelMeta) string {
	//tableSchema := meta.TableSchema // 库名
	tableName := meta.TableName // 表名
	sql := fmt.Sprintf("INSERT INTO %s ", tableName)
	fields := make([]string, 0)
	for _, e := range meta.ModelEList {
		fields = append(fields, e.ModelField)
	}
	values := make([]string, 0)
	for _, e := range meta.ModelEList {
		values = append(values, e.Value)
	}
	nowTime := time.Now().Format("2006.01.02 15:04:05")
	sql += fmt.Sprintf("(id, %s, create_time ) VALUES ", strings.Join(fields, ","))
	sql += fmt.Sprintf("( %s, %s, '%s' )", util.GetId(), strings.Join(values, ","), nowTime)
	return sql
}
